﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Mvc;

namespace eLoan.Models
{
    public static class WorkspaceHelper
    {
        public enum CloseLoanRemark
        {
            [Description("None")]
            None = 0,
            [Description("Excellent")]
            Excellent = 1,
            [Description("Good")]
            Good = 2,
            [Description("Satisfactory")]
            Satisfactory = 3,
            [Description("Risky")]
            Risky = 4,
            [Description("Offset")]
            Offset = 5,
            [Description("Convince for Renewal")]
            ConvinceForRenewal = 6
        }

        public static IEnumerable<SelectListItem> GetCloseLoanRemarkDropDownList()
        {
            var remarks = new List<SelectListItem>();
            remarks.Add(new SelectListItem { Value = ((int)CloseLoanRemark.None).ToString(), Text = CloseLoanRemark.None.ToString() });
            remarks.Add(new SelectListItem { Value = ((int)CloseLoanRemark.Excellent).ToString(), Text = CloseLoanRemark.Excellent.ToString() });
            remarks.Add(new SelectListItem { Value = ((int)CloseLoanRemark.Good).ToString(), Text = CloseLoanRemark.Good.ToString() });
            remarks.Add(new SelectListItem { Value = ((int)CloseLoanRemark.Satisfactory).ToString(), Text = CloseLoanRemark.Satisfactory.ToString() });
            remarks.Add(new SelectListItem { Value = ((int)CloseLoanRemark.Risky).ToString(), Text = CloseLoanRemark.Risky.ToString() });
            remarks.Add(new SelectListItem { Value = ((int)CloseLoanRemark.Offset).ToString(), Text = CloseLoanRemark.Offset.ToString() });
            remarks.Add(new SelectListItem { Value = ((int)CloseLoanRemark.ConvinceForRenewal).ToString(), Text = "Convince for Renewal" });

            return remarks;
        }

        public static LoanPaymentModel GetLoanPaymentModel(DatabaseContext db, string branchCode, int? agentID, string searchText, string userName, bool isCashPayment = true)
        {
            branchCode = (string.IsNullOrEmpty(branchCode)) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            var agents = db.Agents.Where(w => w.BranchCode == branchCode).ToList();

            var agentList = AdministrationHelper.GetAgentDropDownList(agents).ToList();
            agentList.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

            agentID = agentID.HasValue ? agentID : 0;

            var paymentTypes = db.PaymentTypes;

            var model = new LoanPaymentModel();

            model.Agents = agentList;
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);

            model.SelectedBranchCode = branchCode;
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.PaymentDate = DateTime.Today;
            model.SearchText = searchText;
            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            model.PaymentTypes = AdministrationHelper.GetPaymentTypeDropDownList(db.PaymentTypes).ToList();
            model.IsCashPayment = isCashPayment;

            if (isCashPayment)
                model.PaymentItems = GetCashPaymentItems(model.SelectedBranchCode, model.SelectedAgentID, model.SearchText);
            else
                model.PaymentItems = GetChequePaymentItems(model.SelectedBranchCode, model.SelectedAgentID, model.SearchText);

            

            return model;
        }

        public static ChequeClearingModel GetChequeClearingModel(DatabaseContext db, string branchCode, int? agentID, DateTime? fromDate, DateTime? toDate, string userName)
        {
            branchCode = (string.IsNullOrEmpty(branchCode)) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;

            fromDate = fromDate.HasValue ? fromDate : DateTime.Today;
            toDate = toDate.HasValue ? toDate : DateTime.Today;

            var agents = db.Agents.Where(w => w.BranchCode == branchCode).ToList();

            var agentList = AdministrationHelper.GetAgentDropDownList(agents).ToList();
            agentList.Insert(0, new SelectListItem() { Value = "0", Text = "All" });

            agentID = agentID.HasValue ? agentID : 0;

            var model = new ChequeClearingModel();
            model.FromDate = fromDate.GetValueOrDefault();
            model.ToDate = toDate.GetValueOrDefault();
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.Agents = agentList;
            model.SelectedBranchCode = branchCode;
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.Items = GetChequeClearingItems(branchCode, agentID.GetValueOrDefault(), fromDate.GetValueOrDefault(), toDate.GetValueOrDefault());

            return model;

        }

        private static List<BatchPaymentItem> GetCashPaymentItems(string branchCode, int selectedAgentID, string searchText)
        {
            var items = ReportHelper.GetPaymentItems(DateTime.Now, branchCode, selectedAgentID, searchText);
            return items.Where(w => w.PaymentTypeID == (int)AdministrationHelper.PaymentType.CashN ||
                                    w.PaymentTypeID == (int)AdministrationHelper.PaymentType.CashR ||
                                    w.PaymentTypeID == (int)AdministrationHelper.PaymentType.FundTransfer).ToList();
        }

        private static List<BatchPaymentItem> GetChequePaymentItems(string branchCode, int selectedAgentID, string searchText)
        {
            var items = ReportHelper.GetPaymentItems(DateTime.Now, branchCode, selectedAgentID, searchText);
            return items.Where(w => w.PaymentTypeID == (int)AdministrationHelper.PaymentType.ChequeN||
                                    w.PaymentTypeID == (int)AdministrationHelper.PaymentType.ChequeR).ToList();
        }

        private static List<BatchPaymentItem> GetFundTransferPaymentItems(string branchCode, int selectedAgentID, string searchText)
        {
            var items = ReportHelper.GetPaymentItems(DateTime.Now, branchCode, selectedAgentID, searchText);
            return items.Where(w => w.PaymentTypeID == (int)AdministrationHelper.PaymentType.FundTransfer).ToList();
        }

        private static List<BatchPaymentItem> GetPaymentItems(string branchCode, int selectedAgentID, string searchText)
        {
            var paymentItems = new List<BatchPaymentItem>();
            var items = ReportHelper.GetPaymentItems(DateTime.Now, branchCode, selectedAgentID, searchText);
            foreach (var item in items)
            {
                var payment = new BatchPaymentItem();
                payment.DocumentNo = item.DocumentNo;
                payment.BorrowerName = item.BorrowerName;
                payment.Amount = item.Amount;
                payment.ContactNumbers = item.ContactNumbers;
                payment.DocumentDate = item.DocumentDate;
                payment.LoanStatus = item.LoanStatus;
                payment.OutstandingBalance = item.OutstandingBalance;
                payment.PaymentStartDate = item.PaymentStartDate;
                payment.PaymentEndDate = item.PaymentEndDate;
                payment.PaymentTypeID = item.PaymentTypeID;
                payment.LoanTypeDescription = item.LoanTypeDescription;
                payment.PaymentTypeName = item.PaymentTypeName;
                payment.Receivable = item.Receivable;
                payment.ShortOver = item.ShortOver;
                payment.Penalty = item.Penalty;
                payment.PenaltyBalance = item.PenaltyBalance;
                payment.TotalPayment = item.TotalPayment;

                paymentItems.Add(payment);
            }

            return paymentItems;
        }

        public static VoidPaymentModel GetVoidLoanPaymentModel(DatabaseContext db, string branchCode, bool? isAgentSelected, int? agentID, DateTime? datePosted, string searchText, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var agents = db.Agents.Where(w => w.BranchCode == branchCode).ToList();

            agentID = agentID.HasValue ? agentID : agents.Any() ? agents[0].ID : 0;
            isAgentSelected = isAgentSelected.HasValue ? isAgentSelected : true;
            datePosted = datePosted.HasValue ? datePosted : DateTime.Today;

            var model = new VoidPaymentModel();
            model.IsAdmin = AdministrationHelper.GetLoggedInUserRoleID(db, userName) == (int)AdministrationHelper.Role.Admin;
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.SelectedBranchCode = branchCode;
            model.Agents =  AdministrationHelper.GetAgentDropDownList(agents);
            model.IsAgentSelected = isAgentSelected.GetValueOrDefault();
            model.SelectedAgentID = agentID.GetValueOrDefault();
            model.DatePosted = model.IsAdmin ? datePosted.GetValueOrDefault() : DateTime.Today;
            model.SearchText = searchText;
            model.VoidLoanPaymentItems = GetVoidLoanPaymentItems(branchCode, isAgentSelected.GetValueOrDefault(), agentID.GetValueOrDefault(), datePosted.GetValueOrDefault(), searchText);

            return model;
        }

        public static List<VoidPaymentItem> GetVoidLoanPaymentItems(string branchCode, bool? isAgentSelected, int? agentID, DateTime? datePosted, string searchText)
        {
            var items = new List<VoidPaymentItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramIsAgentSelected = new SqlParameter("@IsAgentSelected", isAgentSelected);
                var paramAgentID = new SqlParameter("@AgentID", agentID.GetValueOrDefault());
                var paramDatePosted = new SqlParameter("@DatePosted", datePosted.GetValueOrDefault());
                var paramSearchText = new SqlParameter("@SearchText", searchText);
                
                items = db.Database
                    .SqlQuery<VoidPaymentItem>("sp_GetPaymentsToVoid @BranchCode, @IsAgentSelected, @AgentID, @DatePosted, @SearchText", 
                                                                    paramBranchCode, paramIsAgentSelected, paramAgentID, paramDatePosted, paramSearchText)
                    .ToList();
            }

            return items;
        }

        public static RequestCloseLoanModel GetRequestCloseLoanModel(DatabaseContext db, string branchCode, string searchText, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;
            
            var model = new RequestCloseLoanModel();
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.SelectedBranchCode = branchCode;
            model.SearchText = searchText;
            model.CloseLoanRemarks = GetCloseLoanRemarkDropDownList();
            model.CloseLoanItems = GetRequestCloseLoanItems(branchCode, searchText);

            return model;
        }

        public static CloseLoanModel GetCloseLoanModel(DatabaseContext db, string branchCode, string searchText, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var model = new CloseLoanModel();
            model.Branches = AdministrationHelper.GetBranchDropDownListByUserRole(db, userName);
            model.SelectedBranchCode = branchCode;
            model.SearchText = searchText;
            model.CloseLoanRemarks = GetCloseLoanRemarkDropDownList();
            model.CloseLoanItems = GetCloseLoanItems(branchCode, searchText);

            return model;
        }

        public static LoanPenaltyModel GetLoanPenaltyModel(DatabaseContext db, string branchCode, string searchText, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var model = new LoanPenaltyModel();

            model.Branches = AdministrationHelper.GetBranchDropDownList(db.Branches);
            model.SearchText = searchText;
            model.SelectedBranchCode = branchCode;
            
            var list = GetPenaltyList(branchCode, searchText);

            model.PenaltyList = list.Where(w => w.Sort == (int)AdministrationHelper.PenaltySort.Penalty).ToList();
            model.DelayedLoans = list.Where(w => w.Sort == (int)AdministrationHelper.PenaltySort.Delayed).ToList();
            model.DelinquentLoans = list.Where(w => w.Sort == (int)AdministrationHelper.PenaltySort.Delinquent).ToList();
            
            decimal totalPenalty = 0;
            decimal totalPaidPenalty = 0;
            if (model.PenaltyList.Any())
            {
                totalPenalty = model.PenaltyList.Sum(s => s.TotalPenalty);
                totalPaidPenalty = model.PenaltyList.Sum(s => s.TotalPaidPenalty);
            }
                

            model.TotalPenalty = totalPenalty;
            model.TotalPaidPenalty = totalPaidPenalty;

            return model;
        }

        public static LoanNotificationModel GetLoanNotificationModel(DatabaseContext db, string userName, string branchCode, int? agentID, bool? isModemFound)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;

            var model = new LoanNotificationModel();
            var branches = db.Branches;
            var notifications = db.Notifications;

            model.ModemValidation = isModemFound.GetValueOrDefault() ? string.Empty : "Modem not found!";

            model.Branches = AdministrationHelper.GetBranchDropDownList(branches);
            model.SelectedBranchCode = branchCode;
            
            var agents = db.Agents.Where(w => w.BranchCode == branchCode);
            agentID = agentID.HasValue ? agentID : agents.Any() ? agents.FirstOrDefault<Agent>().ID : 0;

            model.Agents = AdministrationHelper.GetAgentDropDownList(agents);
            model.SelectedAgentID = agentID.GetValueOrDefault();

            model.Notifications = AdministrationHelper.GetNotificationDropDownList(notifications);
            model.SelectedDelayedNotificationID = 1;
            model.SelectedDelingquentNotificationID = 1;
            model.SelectedRenewalNotificationID = 1;

            var list = GetLoansForNotificationList(DateTime.Now, branchCode, agentID.GetValueOrDefault());

            model.DelayedLoans = list.Where(w => w.LoanStatus == AdministrationHelper.PenaltySort.Delayed.ToString()).ToList();
            model.DelinquentLoans = list.Where(w => w.LoanStatus == AdministrationHelper.PenaltySort.Delinquent.ToString()).ToList();

            var loans = db.Loans.Where(w => w.BranchCode == branchCode && w.StatusID == (int)AdministrationHelper.LoanStatus.Closed && w.RemarkID == (int)WorkspaceHelper.CloseLoanRemark.ConvinceForRenewal).ToList();
            model.LoanForRenewal = GetLoansForRenewalModel(loans);

            model.LoanProspects =  GetLoanProspectModel(db.Prospects.ToList(), branchCode, agentID);

            return model;
        }

        public static CollectionReconciliationModel GetCollectionReconciliationModel(DatabaseContext db, string branchCode, DateTime? collectionDate, string searchText, string userName)
        {
            branchCode = string.IsNullOrEmpty(branchCode) ? AdministrationHelper.GetLoggedInUserBranchCode(db, userName) : branchCode;
            collectionDate = collectionDate.HasValue ? collectionDate : DateTime.Today;
            searchText = string.IsNullOrEmpty(searchText) ? string.Empty : searchText;

            var model = new CollectionReconciliationModel();

            model.Branches = AdministrationHelper.GetBranchDropDownList(db.Branches);
            model.SelectedBranchCode = branchCode;
            model.CollectionDate = collectionDate.GetValueOrDefault();
            model.SearchText = searchText;
            model.CollectionReconciliationItems = GetCollectionReconciliationItems(branchCode, collectionDate, searchText);

            return model;
            
        }

        public static CollectionReconciliationAddModel GetCollectionReconciliationAddModel(DatabaseContext db, string branchCode, DateTime? collectionDate)
        {
            var model = new CollectionReconciliationAddModel();
            model.SelectedBranchCode = branchCode;
            model.SelectedBranchName = AdministrationHelper.GetBranchName(branchCode);
            model.CollectionDate = collectionDate.HasValue ? collectionDate.GetValueOrDefault() : DateTime.Today;
            model.CollectionReconciliationItems = new List<CollectionReconciliationQueryModel>();

            var agents = db.Agents.Where(w => w.BranchCode == branchCode && w.IsActive == true);

            foreach (var agent in agents)
            {
                var item = new CollectionReconciliationQueryModel();

                item.Date = collectionDate.GetValueOrDefault();
                item.BranchCode = branchCode;
                item.BranchName = AdministrationHelper.GetBranchName(branchCode);
                item.AgentID = agent.ID;
                item.AgentName = agent.AgentName;
                item.TotalCollectionPerRecord = GetTotalCollectionPerRecord(branchCode, agent.ID, collectionDate.GetValueOrDefault());
                item.CashDeposited = 0;
                item.DeductedToChequeAdvancePayment = 0;
                item.DeductedToChequeSavings = 0;
                item.UsedForLoanRelease = 0;
                item.UsedInWithdrawalOfSavings = 0;
                item.Others = 0;
                item.Remarks = string.Empty;
                item.StatusID = (int)AdministrationHelper.CollectionReconciliationStatus.New;
                item.Status = AdministrationHelper.CollectionReconciliationStatus.New.ToString();

                model.CollectionReconciliationItems.Add(item);
            }

            return model;

        }

        public static decimal GetTotalCollectionPerRecord(string branchCode, int agentID, DateTime collectionDate)
        {
            var list = ReportHelper.GetCollectionOnRecordItems(branchCode, agentID, collectionDate);
            decimal amount = list.Sum(s => s.PostedPayment) + list.Sum(s => s.Penalty);

            return amount;
        }

        public static CollectionReconciliationEditModel GetCollectionReconciliationEditModel(DatabaseContext db, string branchCode, DateTime? collectionDate)
        {
            var model = new CollectionReconciliationEditModel();

            model.CollectionReconciliationItem = db.CollectionReconciliations.Where(w => w.BranchCode == branchCode).SingleOrDefault();

            model.AgentName = AdministrationHelper.GetAgentName(model.CollectionReconciliationItem.AgentID);
            model.BranchName = AdministrationHelper.GetBranchName(model.CollectionReconciliationItem.BranchCode);
            
            return model;

        }
        
        #region Query Models

        public static List<LoansForNotificationModel> GetLoansForNotificationList(DateTime toDate, string branchCode, int agentID)
        {
            var queryModel = new List<LoansForNotificationModel>();

            using (var db = new DatabaseContext())
            {
                var paramToDate = new SqlParameter("@ToDate", toDate);
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);

                queryModel = db.Database
                    .SqlQuery<LoansForNotificationModel>("sp_GetLoansForNotificationList @ToDate, @BranchCode, @AgentID", paramToDate, paramBranchCode, paramAgentID)
                    .ToList();
            }

            return queryModel;
        }

        public static List<ChequeClearingItem> GetChequeClearingItems(string branchCode, int agentID, DateTime fromDate, DateTime toDate)
        {
            var model = new List<ChequeClearingItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramAgentID = new SqlParameter("@AgentID", agentID);
                var paramFromDate = new SqlParameter("@FromDate", fromDate);
                var paramToDate = new SqlParameter("@ToDate", toDate);

                model = db.Database
                    .SqlQuery<ChequeClearingItem>("sp_GetChequeClearingItems @BranchCode, @AgentID, @FromDate, @ToDate", paramBranchCode, paramAgentID, paramFromDate, paramToDate)
                    .ToList();
            }

            return model;
        }

        public static List<LoansForRenewalModel> GetLoansForRenewalModel(List<Loan> loans)
        {
            var model = new List<LoansForRenewalModel>();

            foreach (var loan in loans)
            {
                var item = new LoansForRenewalModel();

                item.AgentID = loan.AgentID;
                item.BorrowerID = loan.BorrowerID = loan.BorrowerID;
                item.BorrowerName = AdministrationHelper.GetBorrowerName(loan.BorrowerID);
                item.ContactNumbers = AdministrationHelper.GetBorrowerContactNumbers(loan.BorrowerID);
                item.BranchCode = loan.BranchCode;
                item.CreditLimit = loan.CreditLimit;
                item.DocumentDate = loan.DocumentDate;
                item.DocumentNo = loan.DocumentNo;
                item.EncodedBy = loan.EncodedBy;
                item.IsActive = loan.IsActive;
                item.LoanAmount = loan.LoanAmount;
                item.LoanInterest = loan.LoanInterest;
                item.LoanTypeID = loan.LoanTypeID;
                item.PaymentEndDate = loan.PaymentEndDate;
                item.PaymentStartDate = loan.PaymentStartDate;
                item.PaymentTypeID = loan.PaymentTypeID;
                item.ProceedTypeID = loan.ProceedTypeID;
                item.RemarkID = loan.RemarkID;
                item.StatusID = loan.StatusID;
                item.LoanStatus = AdministrationHelper.GetLoanStatusName(loan.StatusID);
                item.Remarks = ((WorkspaceHelper.CloseLoanRemark)loan.RemarkID).ToDescription();
                item.Term = loan.Term;
                item.IsSelected = false;

                model.Add(item);
            }

            return model;
        }

        public static List<CloseLoanItem> GetRequestCloseLoanItems(string branchCode, string searchText)
        {
            var queryModel = new List<CloseLoanItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);
                
                queryModel = db.Database
                    .SqlQuery<CloseLoanItem>("sp_GetLoansForClosure @BranchCode, @SearchText", 
                                                                    paramBranchCode, paramSearchText)
                    .ToList();
            }

            return queryModel;
        }

        public static List<CloseLoanItem> GetCloseLoanItems(string branchCode, string searchText)
        {
            var queryModel = new List<CloseLoanItem>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText);

                queryModel = db.Database
                    .SqlQuery<CloseLoanItem>("sp_GetLoansToClose @BranchCode, @SearchText",
                                                                    paramBranchCode, paramSearchText)
                    .ToList();
            }

            return queryModel;
        }

        public static List<LoanPenaltyQueryModel> GetPenaltyList(string branchCode, string searchText)
        {
            var queryModel = new List<LoanPenaltyQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramSearchText = new SqlParameter("@SearchText", searchText.Trim());

                queryModel = db.Database
                    .SqlQuery<LoanPenaltyQueryModel>("sp_GetLoansForPenalty @BranchCode, @SearchText",
                                                            paramBranchCode, paramSearchText)
                    .ToList();
            }

            return queryModel;
        }

        public static List<LoanProspectModel> GetLoanProspectModel(List<Prospect> prospects, string branchCode, int? agentID)
        {
            var items = prospects.Where(w => w.BranchCode == branchCode && w.AgentID == agentID).ToList();
            var model = new List<LoanProspectModel>();

            foreach (var item in items)
            {
                var prospect = new LoanProspectModel();
                prospect.Address = item.Address;
                prospect.AgentID = item.AgentID;
                prospect.BranchCode = item.BranchCode;
                prospect.ContactNumbers = item.ContactNumbers;
                prospect.EmailAddresses = item.EmailAddresses;
                prospect.ID = item.ID;
                prospect.IsActive = item.IsActive;
                prospect.IsSelected = false;
                prospect.LoanOffer = item.LoanOffer;
                prospect.ProspectName = item.ProspectName;
                prospect.DateAdded = item.DateAdded;

                model.Add(prospect);
            }

            return model;
        }

        public static List<CollectionReconciliationQueryModel> GetCollectionReconciliationItems(string branchCode, DateTime? collectionDate, string searchText)
        {
            var queryModel = new List<CollectionReconciliationQueryModel>();

            using (var db = new DatabaseContext())
            {
                var paramBranchCode = new SqlParameter("@BranchCode", branchCode);
                var paramCollectionDate = new SqlParameter("@CollectionDate", collectionDate);
                var paramSearchText = new SqlParameter("@SearchText", searchText.Trim());

                queryModel = db.Database
                    .SqlQuery<CollectionReconciliationQueryModel>("sp_GetCollectionReconciliationItems @BranchCode, @CollectionDate, @SearchText",
                                                            paramBranchCode, paramCollectionDate, paramSearchText)
                    .ToList();
            }

            return queryModel;
        }

        #endregion

    }
}